﻿using MySchool;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace MyKTV.wxt
{
    public partial class Form1 : Form
    {
        DBHelper dBHelper = new DBHelper();
        DataSet ds = new DataSet();
        SqlDataAdapter adapter;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {


            dBHelper.OpenConnection();

            string sql = "select song_name,song_ab,song_word_count,songtype_name,singer_name,song_url,song_play_count from song_info inner join song_type on  song_info.songtype_id= song_type.songtype_id inner join singer_info on song_info.singer_id= singer_info.singer_id";

            adapter = new SqlDataAdapter(sql, dBHelper.Connection);
            adapter.Fill(ds, "song_info");

            this.dataGridView1.DataSource = ds.Tables["song_info"];
        }

        private void contextMenuStrip1_Opening(object sender, CancelEventArgs e)
        {

        }

        private void 修改ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Form000003 frm = new Form000003();

            //frm.box0 = this.dataGridView1.SelectedRows[0].Cells["song_id"].Value.ToString();
            frm.box1 = this.dataGridView1.SelectedRows[0].Cells["song_name"].Value.ToString();
            frm.box2 = this.dataGridView1.SelectedRows[0].Cells["song_ab"].Value.ToString();
            frm.box3 = this.dataGridView1.SelectedRows[0].Cells["song_word_count"].Value.ToString();
            frm.box4 = this.dataGridView1.SelectedRows[0].Cells["songtype_name"].Value.ToString();
            frm.box5 = this.dataGridView1.SelectedRows[0].Cells["singer_name"].Value.ToString();
            frm.box6 = this.dataGridView1.SelectedRows[0].Cells["song_url"].Value.ToString();
            frm.box7 = this.dataGridView1.SelectedRows[0].Cells["song_play_count"].Value.ToString();
            frm.ShowDialog();
            ds.Tables["song_info"].Clear();
            String str1 = "select song_id,song_name,song_ab,song_word_count,songtype_name,singer_name,song_url,song_play_count from song_info " +
            "inner join  song_type on song_type.songtype_id = song_info.songtype_id" +
            " inner join  singer_info on song_info.singer_id = singer_info.singer_id order by song_id desc";            
            SqlDataAdapter adapter = new SqlDataAdapter(str1, dBHelper.Connection);
            //填充
            adapter.Fill(ds, "song_info");
            this.dataGridView1.DataSource = ds.Tables["song_info"];
        }

        private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {
               DialogResult dev = MessageBox.Show("是否删除", "错误提示框", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);

            if (dev == DialogResult.Yes)
            {
                dBHelper.OpenConnection();
                string song_name = this.dataGridView1.SelectedRows[0].Cells["song_name"].Value.ToString();
                string str = "delete from  song_info where song_name=N'" + song_name + "'";
                SqlCommand command = new SqlCommand(str, dBHelper.Connection);
                int sc = command.ExecuteNonQuery();
                if (sc > 0)
                {
                    MessageBox.Show("删除成功");
                    ds.Tables["song_info"].Clear();
                    string yi = "select song_name,song_ab,song_word_count,songtype_name,singer_name,song_url,song_play_count from song_info inner join song_type on  song_info.songtype_id= song_type.songtype_id inner join singer_info on song_info.singer_id= singer_info.singer_id ";
                    SqlDataAdapter adapter = new SqlDataAdapter(yi, dBHelper.Connection);
                    adapter.Fill(ds, "song_info");
                    this.dataGridView1.DataSource = ds.Tables["song_info"];
                    dBHelper.CloseConnection();
                }
                else
                {
                    MessageBox.Show("删除失败1");
                }
            }

        }
        private void button1_Click(object sender, EventArgs e)
        {
            if (comboBox1.Text == "全部")
            {
                if (textBoxEx1.Text != null)
                {
                    MessageBox.Show("请选择查询类型");
                    return;
                }
            }
            ds.Tables["song_info"].Clear();
            string sql = "";
            if (this.textBoxEx1.Text.Equals(string.Empty))
            {
                MessageBox.Show("请输入查询条件");
                return;
            }
            else
            {
                if (this.comboBox1.Text == "按歌名查询")
                {
                    sql = "select song_name,song_ab,song_word_count,songtype_name,singer_name,song_url,song_play_count from song_info inner join song_type on  song_info.songtype_id= song_type.songtype_id inner join singer_info on song_info.singer_id= singer_info.singer_id where song_name like N'%" + this.textBoxEx1.Text.Trim() + "%'";
                    adapter = new SqlDataAdapter(sql, dBHelper.Connection);
                    int p = adapter.Fill(ds, "song_info");
                    if (p == 0)
                    {
                        MessageBox.Show("没有找到");
                    }
                    this.dataGridView1.DataSource = ds.Tables["song_info"];
                }
                else if (this.comboBox1.Text == "按歌手查询")
                {
                    sql = "select song_name,song_ab,song_word_count,songtype_name,singer_name,song_url,song_play_count from song_info inner join song_type on  song_info.songtype_id= song_type.songtype_id inner join singer_info on song_info.singer_id= singer_info.singer_id where singer_name like N'%"+this.textBoxEx1.Text.Trim()+"%'";
                    adapter = new SqlDataAdapter(sql, dBHelper.Connection);
                    int p = adapter.Fill(ds, "song_info");
                    if (p == 0)
                    {
                        MessageBox.Show("没有找到");
                    }
                    this.dataGridView1.DataSource = ds.Tables["song_info"];
                }
                else if (this.comboBox1.Text == "按歌名字数查询")

                {
                    sql = "select song_name,song_ab,song_word_count,songtype_name,singer_name,song_url,song_play_count from song_info inner join song_type on  song_info.songtype_id= song_type.songtype_id inner join singer_info on song_info.singer_id= singer_info.singer_id where song_word_count like '%" + this.textBoxEx1.Text.Trim() + "%'";
                    adapter = new SqlDataAdapter(sql, dBHelper.Connection);
                    int p = adapter.Fill(ds, "song_info");
                    if (p == 0)
                    {
                        MessageBox.Show("没有找到");
                    }
                    this.dataGridView1.DataSource = ds.Tables["song_info"];
                }
                else if (this.comboBox1.Text == "按拼音查找")
                {
                    sql = "select song_name,song_ab,song_word_count,songtype_name,singer_name,song_url,song_play_count from song_info inner join song_type on  song_info.songtype_id= song_type.songtype_id inner join singer_info on song_info.singer_id= singer_info.singer_id where song_ab like '%" + this.textBoxEx1.Text.Trim() + "%'";
                    adapter = new SqlDataAdapter(sql, dBHelper.Connection);
                    int p = adapter.Fill(ds, "song_info");
                    
                    if (p == 0)

                    {
                        MessageBox.Show("没有找到");

                    }
                    this.dataGridView1.DataSource = ds.Tables["song_info"];
                }
            }
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }
    }
}
